package com.string.actortv.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper {

    private final static String DATABASE_NAME = "Library";
    private final static int DATABASE_VERSION = 2;
    private final static String HISTORY_TABLE_NAME = "play_history";
    private final static String FAVORITE_TABLE_NAME = "play_favorite";

    //构造函数，创建数据库
    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //建表
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + HISTORY_TABLE_NAME
                + "(_id INTEGER PRIMARY KEY,"
                + " title VARCHAR(30)  NOT NULL,"
                + " img_url VARCHAR(20),"
                + " url VARCHAR(20),"
                + " play_url VARCHAR(20),"
                + " times INTEGER,"
                + " player_type INTEGER"
                + ")";
        db.execSQL(sql);
        String sql2 = "CREATE TABLE " + FAVORITE_TABLE_NAME
                + "(_id INTEGER PRIMARY KEY,"
                + " title VARCHAR(30)  NOT NULL,"
                + " img_url VARCHAR(20),"
                + " url VARCHAR(20),"
                + " player_type INTEGER"
                + ")";
        db.execSQL(sql2);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS " + HISTORY_TABLE_NAME;
        String sql2 = "DROP TABLE IF EXISTS " + FAVORITE_TABLE_NAME;
        db.execSQL(sql);
        db.execSQL(sql2);
        onCreate(db);
    }

    /**
     * 获取列表
     */
    public Cursor selectHistory() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(HISTORY_TABLE_NAME, null, null,
                null, null, null, "_id desc");
    }

    /**
     * 获取列表
     */
    public Cursor selectFavorite() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.query(FAVORITE_TABLE_NAME, null, null,
                null, null, null, "_id desc");
    }

    /**
     * 插入一条记录
     *
     * @param title
     * @param imgUrl
     * @param url
     * @param playUrl
     * @param times
     * @param playerType
     * @return
     */
    public long insertHistory(String title, String imgUrl, String url, String playUrl, long times, int playerType) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("title", title);
        cv.put("img_url", imgUrl);
        cv.put("url", url);
        cv.put("play_url", playUrl);
        cv.put("times", times);
        cv.put("player_type", playerType);
        return db.insert(HISTORY_TABLE_NAME, null, cv);
    }

    /**
     * 插入一条收藏
     *
     * @param title
     * @param imgUrl
     * @param url
     * @param playerType
     * @return
     */
    public long insertFavorite(String title, String imgUrl, String url, int playerType) {
        if (queryFavoriteByUrl(url).getCount() == 0) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put("title", title);
            cv.put("img_url", imgUrl);
            cv.put("url", url);
            cv.put("player_type", playerType);
            return db.insert(FAVORITE_TABLE_NAME, null, cv);
        } else {
            return 0;
        }
    }

    //根据条件查询
    public Cursor queryHistoryByUrl(String url) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] args = new String[1];
        args[0] = url;
        return db.rawQuery("SELECT * FROM " + HISTORY_TABLE_NAME + " WHERE url=?", args);
    }

    //根据条件查询
    public Cursor queryFavoriteByUrl(String url) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] args = new String[1];
        args[0] = url;
        return db.rawQuery("SELECT * FROM " + FAVORITE_TABLE_NAME + " WHERE url=?", args);
    }

    //删除记录
    public void deleteFavoriteByUrl(String url) {
        SQLiteDatabase db = this.getWritableDatabase();
        String where = "url = ?";
        String[] whereValue = {url};
        db.delete(FAVORITE_TABLE_NAME, where, whereValue);
    }

    //删除记录
    public void deleteHistoryByUrl(String url) {
        SQLiteDatabase db = this.getWritableDatabase();
        String where = "url = ?";
        String[] whereValue = {url};
        db.delete(HISTORY_TABLE_NAME, where, whereValue);
    }

    //更新记录播放时长
    public void updateHistoryTimes(String url, long times, String percent) {
        SQLiteDatabase db = this.getWritableDatabase();
        String[] args = new String[1];
        args[0] = url;
        Cursor cursor = db.rawQuery("SELECT * FROM " + HISTORY_TABLE_NAME + " WHERE url=?", args);
        cursor.moveToPosition(0);
        String newName = cursor.getString(1).split("-")[0] + "-" + percent;
        String where = "url = ?";
        String[] whereValue = {url};
        ContentValues cv = new ContentValues();
        cv.put("times", times);
        cv.put("title", newName);
        db.update(HISTORY_TABLE_NAME, cv, where, whereValue);
    }
}
